package org.niit.dao

import org.niit.util.JDBCUtil
import java.sql.{ResultSet, Timestamp}
import java.util.Date

/**
 * 实时分析DAO，用于将实时分析结果写入数据库
 */
object RealtimeAnalysisDao {

  /**
   * 保存实时订单统计数据
   *
   * @param totalOrders 订单总量
   * @param ordersPerMinute 每分钟订单数
   * @param avgAmount 平均订单金额
   * @param activeCities 活跃城市数
   */
  def saveOrderStats(totalOrders: Int, ordersPerMinute: Double, avgAmount: Double, activeCities: Int): Unit = {
    val sql = "INSERT INTO realtime_order_stats (total_orders, orders_per_minute, avg_amount, active_cities, update_time) VALUES (?, ?, ?, ?, ?)"
    val params = Array(totalOrders, ordersPerMinute, avgAmount, activeCities, new Timestamp(new Date().getTime))
    JDBCUtil.executeUpdate(sql, params)
  }

  /**
   * 保存实时地区分布分析结果，以城市为主键，遇到相同主键时合并数据
   *
   * @param city 城市
   * @param orderCount 订单数量
   * @param percentage 百分比
   */
  def saveRealtimeRegion(city: String, orderCount: Int, percentage: Double): Unit = {
    // 先查询是否存在相同城市的记录
    val querySQL = "SELECT order_count, percentage FROM realtime_region WHERE city = ?"
    val queryParams: Array[Any] = Array(city)
    val resultSet = JDBCUtil.executeQuery(querySQL, queryParams)
    
    val currentTime = new Timestamp(new Date().getTime)
    
    if (resultSet.next()) {
      // 存在记录，进行更新，订单数量相加，百分比取平均
      val existingOrderCount = resultSet.getInt("order_count")
      val existingPercentage = resultSet.getDouble("percentage")
      val newOrderCount = existingOrderCount + orderCount
      val newPercentage = (existingPercentage + percentage) / 2.0
      
      val updateSQL = "UPDATE realtime_region SET order_count = ?, percentage = ?, update_time = ? WHERE city = ?"
      val updateParams: Array[Any] = Array(newOrderCount, newPercentage, currentTime, city)
      JDBCUtil.executeUpdate(updateSQL, updateParams)
    } else {
      // 不存在记录，插入新数据
      val insertSQL = "INSERT INTO realtime_region (city, order_count, percentage, update_time) VALUES (?, ?, ?, ?)"
      val insertParams: Array[Any] = Array(city, orderCount, percentage, currentTime)
      JDBCUtil.executeUpdate(insertSQL, insertParams)
    }
    
    // 关闭ResultSet
    resultSet.close()
  }

  /**
   * 保存实时菜品类别分析结果，以类别为主键，遇到相同主键时合并数据
   *
   * @param category 菜品类别
   * @param orderCount 订单数量
   * @param percentage 百分比
   */
  def saveRealtimeCategory(category: String, orderCount: Int, percentage: Double): Unit = {
    // 先查询是否存在相同类别的记录
    val querySQL = "SELECT order_count, percentage FROM realtime_category WHERE category = ?"
    val queryParams: Array[Any] = Array(category)
    val resultSet = JDBCUtil.executeQuery(querySQL, queryParams)
    
    val currentTime = new Timestamp(new Date().getTime)
    
    if (resultSet.next()) {
      // 存在记录，进行更新，订单数量相加，百分比取平均
      val existingOrderCount = resultSet.getInt("order_count")
      val existingPercentage = resultSet.getDouble("percentage")
      val newOrderCount = existingOrderCount + orderCount
      val newPercentage = (existingPercentage + percentage) / 2.0
      
      val updateSQL = "UPDATE realtime_category SET order_count = ?, percentage = ?, update_time = ? WHERE category = ?"
      val updateParams: Array[Any] = Array(newOrderCount, newPercentage, currentTime, category)
      JDBCUtil.executeUpdate(updateSQL, updateParams)
    } else {
      // 不存在记录，插入新数据
      val insertSQL = "INSERT INTO realtime_category (category, order_count, percentage, update_time) VALUES (?, ?, ?, ?)"
      val insertParams: Array[Any] = Array(category, orderCount, percentage, currentTime)
      JDBCUtil.executeUpdate(insertSQL, insertParams)
    }
    
    // 关闭ResultSet
    resultSet.close()
  }

  /**
   * 保存实时活跃时段分析结果，以时间段为主键，遇到相同主键时合并数据
   *
   * @param timePeriod 时间段
   * @param orderCount 订单数量
   * @param percentage 百分比
   */
  def saveRealtimeTimePeriod(timePeriod: String, orderCount: Int, percentage: Double): Unit = {
    // 先查询是否存在相同时间段的记录
    val querySQL = "SELECT order_count, percentage FROM realtime_time_period WHERE time_period = ?"
    val queryParams: Array[Any] = Array(timePeriod)
    val resultSet = JDBCUtil.executeQuery(querySQL, queryParams)
    
    val currentTime = new Timestamp(new Date().getTime)
    
    if (resultSet.next()) {
      // 存在记录，进行更新，订单数量相加，百分比取平均
      val existingOrderCount = resultSet.getInt("order_count")
      val existingPercentage = resultSet.getDouble("percentage")
      val newOrderCount = existingOrderCount + orderCount
      val newPercentage = (existingPercentage + percentage) / 2.0
      
      val updateSQL = "UPDATE realtime_time_period SET order_count = ?, percentage = ?, update_time = ? WHERE time_period = ?"
      val updateParams: Array[Any] = Array(newOrderCount, newPercentage, currentTime, timePeriod)
      JDBCUtil.executeUpdate(updateSQL, updateParams)
    } else {
      // 不存在记录，插入新数据
      val insertSQL = "INSERT INTO realtime_time_period (time_period, order_count, percentage, update_time) VALUES (?, ?, ?, ?)"
      val insertParams: Array[Any] = Array(timePeriod, orderCount, percentage, currentTime)
      JDBCUtil.executeUpdate(insertSQL, insertParams)
    }
    
    // 关闭ResultSet
    resultSet.close()
  }

  /**
   * 保存实时商铺订单分析结果，以商铺名称为主键，遇到相同主键时合并数据
   *
   * @param merchantName 商铺名称
   * @param orderCount 订单数量
   * @param percentage 百分比
   */
  def saveRealtimeMerchant(merchantName: String, orderCount: Int, percentage: Double): Unit = {
    // 先查询是否存在相同商铺的记录
    val querySQL = "SELECT order_count, percentage FROM realtime_merchant WHERE merchant_name = ?"
    val queryParams: Array[Any] = Array(merchantName)
    val resultSet = JDBCUtil.executeQuery(querySQL, queryParams)
    
    val currentTime = new Timestamp(new Date().getTime)
    
    if (resultSet.next()) {
      // 存在记录，进行更新，订单数量相加，百分比取平均
      val existingOrderCount = resultSet.getInt("order_count")
      val existingPercentage = resultSet.getDouble("percentage")
      val newOrderCount = existingOrderCount + orderCount
      val newPercentage = (existingPercentage + percentage) / 2.0
      
      val updateSQL = "UPDATE realtime_merchant SET order_count = ?, percentage = ?, update_time = ? WHERE merchant_name = ?"
      val updateParams: Array[Any] = Array(newOrderCount, newPercentage, currentTime, merchantName)
      JDBCUtil.executeUpdate(updateSQL, updateParams)
    } else {
      // 不存在记录，插入新数据
      val insertSQL = "INSERT INTO realtime_merchant (merchant_name, order_count, percentage, update_time) VALUES (?, ?, ?, ?)"
      val insertParams: Array[Any] = Array(merchantName, orderCount, percentage, currentTime)
      JDBCUtil.executeUpdate(insertSQL, insertParams)
    }
    
    // 关闭ResultSet
    resultSet.close()
  }

  /**
   * 保存实时平台分析结果，以平台为主键，遇到相同主键时合并数据
   *
   * @param platform 平台
   * @param orderCount 订单数量
   * @param percentage 百分比
   */
  def saveRealtimePlatform(platform: String, orderCount: Int, percentage: Double): Unit = {
    // 先查询是否存在相同平台的记录
    val querySQL = "SELECT order_count, percentage FROM realtime_platform WHERE platform = ?"
    val queryParams: Array[Any] = Array(platform)
    val resultSet = JDBCUtil.executeQuery(querySQL, queryParams)
    
    val currentTime = new Timestamp(new Date().getTime)
    
    if (resultSet.next()) {
      // 存在记录，进行更新，订单数量相加，百分比取平均
      val existingOrderCount = resultSet.getInt("order_count")
      val existingPercentage = resultSet.getDouble("percentage")
      val newOrderCount = existingOrderCount + orderCount
      val newPercentage = (existingPercentage + percentage) / 2.0
      
      val updateSQL = "UPDATE realtime_platform SET order_count = ?, percentage = ?, update_time = ? WHERE platform = ?"
      val updateParams: Array[Any] = Array(newOrderCount, newPercentage, currentTime, platform)
      JDBCUtil.executeUpdate(updateSQL, updateParams)
    } else {
      // 不存在记录，插入新数据
      val insertSQL = "INSERT INTO realtime_platform (platform, order_count, percentage, update_time) VALUES (?, ?, ?, ?)"
      val insertParams: Array[Any] = Array(platform, orderCount, percentage, currentTime)
      JDBCUtil.executeUpdate(insertSQL, insertParams)
    }
    
    // 关闭ResultSet
    resultSet.close()
  }

  /**
   * 批量保存实时分析结果
   *
   * @param tableName 表名
   * @param columns 列名
   * @param data 数据列表，每个元素是一行数据
   */
  def batchSaveRealtimeResult(tableName: String, columns: String, data: List[Array[Any]]): Unit = {
    val sql = s"INSERT INTO $tableName ($columns) VALUES (${("?" * data.head.length).split("").mkString(", ")})"
    JDBCUtil.executeBatchUpdate(sql, data)
  }

  /**
   * 清空实时分析表以准备新的批次数据
   *
   * @param tableName 表名
   */
  def clearRealtimeTable(tableName: String): Unit = {
    val sql = s"DELETE FROM $tableName"
    JDBCUtil.executeUpdate(sql, Array())
  }
} 